/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.game.persistence;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import net.groupme.exceptions.InsertFailedException;
import net.groupme.exceptions.UpdateFailedException;
import net.groupme.exceptions.UserNotExistingException;
import net.groupme.game.GAME_CONSTANTS;
import net.groupme.game.GroupMeGame;
import net.groupme.game.GroupMeGameDAO;
import net.groupme.model.Group;
import net.groupme.model.GroupVisualization;
import net.groupme.model.MODEL_CONSTANTS;
import net.groupme.model.ObjectFactory;
import net.groupme.model.Resource;
import net.groupme.model.ResourceVisualization;
import net.groupme.model.Tag;
import net.groupme.model.User;
import net.groupme.model.dao.DATABASE_CONSTANTS;
import net.groupme.model.dao.GroupDAOImpl;
import net.groupme.model.dao.ResourceDAOImpl;
import net.groupme.model.dao.mem.GroupDAOMemImpl;
import net.groupme.similarityComputer.SimilarityComputerFactory;

/**
 * created on Feb 28, 2008
 * 
 * This class provides methods to store a {@link GroupMeGame}, which is based on
 * a groups of type {@link GroupDAOMemImpl}, into the database.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: fabian $
 * 
 * @version $Revision: 1.4 $ $Date: 2008-03-04 17:06:26 $
 */
public class GamePersistence {
	
	/** Logger for this class and subclasses */
	private static final Log logger = LogFactory.getLog(GamePersistence.class);
	
	/**
	 * This method stores the {@link GroupMeGame} and the corresponding {@link GroupDAOMemImpl} groups
	 * into the database.
	 * @param game the game to store.
	 * @return the GroupMeGame that was stored in the database
	 * @throws InsertFailedException failed to store the game
	 * @throws UpdateFailedException failed to update e.g. groupResource bindings, etc.
	 */
	public static final GroupMeGame store(GroupMeGame game) throws InsertFailedException, UpdateFailedException{
		String insertGameSQL = 
			"INSERT INTO " + DATABASE_CONSTANTS.TABLE_GAME //$NON-NLS-1$
			+ " (" 	+ DATABASE_CONSTANTS.GAME_TOPIC + ", " 
					+ DATABASE_CONSTANTS.GAME_CREATOR + ", "
					+ DATABASE_CONSTANTS.GAME_OPPONENT + ", " 
					+ DATABASE_CONSTANTS.GAME_CREATORS_GROUP + ", " 
					+ DATABASE_CONSTANTS.GAME_OPPONENTS_GROUP + ", " 
					+ DATABASE_CONSTANTS.GAME_SCORE+ ", " 
					+ DATABASE_CONSTANTS.GAME_DURATION + ", " 
					+ DATABASE_CONSTANTS.GAME_SETTINGS +  ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; //$NON-NLS-1$ 
		
		SqlUpdate su = new SqlUpdate(
				ObjectFactory.ds,insertGameSQL);
		
		su.declareParameter(new SqlParameter("topic", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("creator", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("opponent", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("creatorsGroup", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("opponentsGroup", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("score", Types.DOUBLE)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("duration", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("settings", Types.VARCHAR)); //$NON-NLS-1$
		su.compile();
		Object[] oa = {game.getGametopic(),
				game.getCreator().getId(),
				(game.getOpponent() == null ? null : game.getOpponent().getId()), 
				game.getCreatorsGroup().getId(),
				game.getOpponentsGroup().getId(),
				game.getOverallScore(),
				GAME_CONSTANTS.GAME_TIME_LIMIT,
				SimilarityComputerFactory.getSimilarityComputer(game.getCreatorsGroup(), game.getOpponentsGroup()).printSettings()
				};
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder(); 
		int count = su.update(oa, keyHolder);
		if(count == 0){
			logger.info("Storage of game failed!");
			throw new InsertFailedException("Failed to store the game about: " + (game == null ? "[game is null!!]" : game.getGametopic()));
		}
		Long id = keyHolder.getKey().longValue();
		convertInMemDAOToDatabaseDAO(game.getCreatorsGroup(), game.getCreator());
		convertInMemDAOToDatabaseDAO(game.getOpponentsGroup(), game.getOpponent());
		return readGameFromDB(id);
	}
	
	/**
	 * This method converts a group, which is based on a {@link GroupDAOMemImpl}, into a
	 * group, which is based on {@link GroupDAOImpl}. 
	 * @param group the group to convert
	 * @param owner the owner of the group
	 * @return a group, which is based on a {@link GroupDAOMemImpl}, into a
	 * group, which is based on {@link GroupDAOImpl}.
	 * @throws UpdateFailedException failed to update the group or the contained resources within the DB
	 * @throws InsertFailedException failed to insert e.g. tag assignment...
	 */
	public static final Group convertInMemDAOToDatabaseDAO(Group group, User owner) throws UpdateFailedException, InsertFailedException{
		Group convertedGroup = ObjectFactory.getGroup(group.getId());
		convertedGroup.setName(group.getName());
		convertedGroup.setDescription(group.getDescription());
		convertedGroup.setType(group.getType());
		owner.addGroup(group, MODEL_CONSTANTS.ROLE_OWNER);
		
		//tags:
		for(Tag tag: group.getTags()){//tags are in the DB (see GameAjaxController.addTag...)
			convertedGroup.addTag(owner, tag);
		}

		//resources
		for(Resource resource: group.getResources()){
			convertedGroup.addResource(convertInMemDAOToDatabaseDAO(resource, group, owner));
		}
		
		GroupVisualization convertedGVisu = ObjectFactory.getGroupVisualization(group.getStandardVisualization().getId());
		convertedGVisu.setName(group.getStandardVisualization().getName());
		convertedGVisu.setDescription(group.getStandardVisualization().getDescription());
		
		//resource visualizations:
		for(ResourceVisualization resVisu: group.getStandardVisualization().getResourceVisualizations()){
			ResourceVisualization convertedResVisu = ObjectFactory.getResourceVisualization(resVisu.getId());
			//update only the numbers:
			convertedResVisu.setHeight(resVisu.getHeight());
			convertedResVisu.setWidth(resVisu.getWidth());
			convertedResVisu.setPositionX(resVisu.getPositionX());
			convertedResVisu.setPositionY(resVisu.getPositionY());
			convertedResVisu.setZAxis(resVisu.getZAxis());
			
			convertedResVisu.setRenderer(resVisu.getRenderer());
			
			convertedGVisu.addResourceVisualization(convertedResVisu);
		}
		convertedGroup.addVisualization(convertedGVisu, owner);
		convertedGroup.setStandardVisualization(convertedGVisu);
		
		return convertedGroup;
	}

	
	/**
	 * This method converts a resource, which is based on a {@link ResourceDAOImpl}, into a
	 * resource, which is based on {@link GroupDAOMemImpll}. 
	 * @param resource the resource to convert
	 * @param group the group in which tags were assigned to the resource
	 * @param tagger the tagger
	 * @return a resource, which is based on a {@link ResourceDAOImpl}, into a
	 * resource, which is based on {@link GroupDAOMemImpll}. 
	 * @throws UpdateFailedException failed to update the resource within database
	 * @throws InsertFailedException failed to insert e.g. tag assignment...
	 */
	public static final Resource convertInMemDAOToDatabaseDAO(Resource resource, Group group, User tagger) throws UpdateFailedException, InsertFailedException{
		Resource convertedResource = ObjectFactory.getResource(resource.getId());
		convertedResource.setName(resource.getName());
		convertedResource.setDescription(resource.getDescription());
		convertedResource.setType(resource.getType());
		convertedResource.setURL(resource.getURL());
		for(Tag tag: resource.getTags()){//tags are in the DB (see GameAjaxController.addTag...)
			convertedResource.addTag(tagger, tag, group);
		}
		return convertedResource;
	}
	
	/**
	 * This method returns the overall rank of a certain game
	 * @param game the game
	 * @return the rank of a certain game
	 */
	public static int getGameRankOverall(GroupMeGame game){
		List<GroupMeGame> highscore = getHighScore(1000000000);
		return highscore.indexOf(game) + 1;
	}
	
	/**
	 * This method returns today's rank of a certain game
	 * @param game the game
	 * @return the rank of a certain game
	 */
	public static int getGameRankToday(GroupMeGame game){
		List<GroupMeGame> highscore = getTodaysHighScore(1000000000);
		return highscore.indexOf(game) + 1;
	}
	
	/**
	 * This method returns the overall high score.
	 * @param topK show top k games with highest score
	 * @return method returns the overall high score.
	 */
	public static final List<GroupMeGame> getHighScore(int topK){
		List<GroupMeGame> highscore = readGamesFromDB(null, null, null);
		if(highscore.size() > topK){
			return highscore.subList(0, topK);
		}
		return highscore;
	}
	
	/**
	 * This method returns today's high score.
	 * @param topK show top k games with highest score
	 * 
	 * @return method returns today's high score.
	 */
	public static final List<GroupMeGame> getTodaysHighScore(int topK){
		List<GroupMeGame> highscore = readGamesFromDB(null, new Date(System.currentTimeMillis()), null);
		if(highscore.size() > topK){
			return highscore.subList(0, topK);
		}
		return highscore;
	}
	
	/**
	 * This method returns the high score for a certain period.
	 * @param topK show top k games with highest score
	 * @param from receive games older or equals old than <code>from</code>, set to <code>null</code> if you don't want to restrict something
	 * @param to receive games younger or equals old than <code>to</code>, set to <code>null</code> if you don't want to restrict something
	 *
	 * @return method returns the high score for a certain period.
	 */
	public static final List<GroupMeGame> getHighScore(int topK, Date from, Date to){
		List<GroupMeGame> highscore = readGamesFromDB(null, from, to);
		if(highscore.size() > topK){
			return highscore.subList(0, topK);
		}
		return highscore;
	}
	
	/**
	 * Reads games from the database ordered by score.
	 * @param user leave this parameter <code>null</code> if you want to receive games of all users, otherwise specify the user, whose games you want to receive
	 * @param from receive games older or equals old than <code>from</code>, set to <code>null</code> if you don't want to restrict something
	 * @param to receive games younger or equals old than <code>to</code>, set to <code>null</code> if you don't want to restrict something
	 * @return games from the database that hold the restriction
	 */
	public static final List<GroupMeGame> readGamesFromDB(User user, Date from, Date to){
		List<GroupMeGame> games = new ArrayList<GroupMeGame>();
		Statement sqlStatement = null;
		ResultSet result = null;
		
		//Constructing the query...
		String query = "SELECT "
			+ DATABASE_CONSTANTS.GAME_ID + ", " 
			+ DATABASE_CONSTANTS.GAME_TOPIC + ", " 
			+ DATABASE_CONSTANTS.GAME_CREATOR + ", "
			+ DATABASE_CONSTANTS.GAME_OPPONENT + ", " 
			+ DATABASE_CONSTANTS.GAME_CREATORS_GROUP + ", " 
			+ DATABASE_CONSTANTS.GAME_OPPONENTS_GROUP + ", " 
			+ DATABASE_CONSTANTS.GAME_SCORE + ", " 
			+ DATABASE_CONSTANTS.GAME_TIMESTAMP 
			+ " FROM " + DATABASE_CONSTANTS.TABLE_GAME;
		if(user != null){
			query += " WHERE (" + DATABASE_CONSTANTS.GAME_CREATOR + " = '" + user.getId() + "' OR " + DATABASE_CONSTANTS.GAME_OPPONENT + " = '" + user.getId() + "') ";
		}
		if(from != null){
			if(!query.contains("WHERE")){
				query += " WHERE ";
			}else{
				query += " AND ";
			}
			query += DATABASE_CONSTANTS.GAME_TIMESTAMP + " >= '" + from.toString() + "' ";
		}
		if(to != null){
			if(!query.contains("WHERE")){
				query += " WHERE ";
			}else{
				query += " AND ";
			}
			query +=  DATABASE_CONSTANTS.GAME_TIMESTAMP + " <= '" + to.toString() + "' ";
		}
		query += " order by " + DATABASE_CONSTANTS.GAME_SCORE + " desc";
		
		//running the query:
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery(query);
			while (result.next()) {
				try {
					GroupMeGame game = new GroupMeGameDAO(result.getLong(1), result.getString(2), 
							ObjectFactory.getUser(result.getInt(3)), ObjectFactory.getUser(result.getInt(4)),
							ObjectFactory.getGroup(result.getString(5)), ObjectFactory.getGroup(result.getString(6)),
							result.getInt(7), result.getDate(8));
					games.add(game);
				} catch (UserNotExistingException e) {
					e.printStackTrace();
					logger.error(e);
					logger.error("Failed to read game from DB...");
				}
			}
			ObjectFactory.ds.getConnection().close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return games;
	}
	
	/**
	 * Reads game with given id (DB ID and not the temporal ID) from the database.
	 * @param id the id (DB ID and not the temporal ID)
	 * @return game with given id (DB ID and not the temporal ID) from the database, or <code>null</code> if the game does not exist.
	 */
	public static final GroupMeGame readGameFromDB(Long id){
		Statement sqlStatement = null;
		ResultSet result = null;
		
		//Constructing the query...
		String query = "SELECT "
			+ DATABASE_CONSTANTS.GAME_ID + ", " 
			+ DATABASE_CONSTANTS.GAME_TOPIC + ", " 
			+ DATABASE_CONSTANTS.GAME_CREATOR + ", "
			+ DATABASE_CONSTANTS.GAME_OPPONENT + ", " 
			+ DATABASE_CONSTANTS.GAME_CREATORS_GROUP + ", " 
			+ DATABASE_CONSTANTS.GAME_OPPONENTS_GROUP + ", " 
			+ DATABASE_CONSTANTS.GAME_SCORE + ", " 
			+ DATABASE_CONSTANTS.GAME_TIMESTAMP 
			+ " FROM " + DATABASE_CONSTANTS.TABLE_GAME 
			+ " WHERE " + DATABASE_CONSTANTS.GAME_ID + " = '" + id + "' ";

		//running the query:
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			result = sqlStatement.executeQuery(query);
			while (result.next()) {
				try {
					GroupMeGame game = new GroupMeGameDAO(result.getLong(1), result.getString(2), 
							ObjectFactory.getUser(result.getInt(3)), ObjectFactory.getUser(result.getInt(4)),
							ObjectFactory.getGroup(result.getString(5)), ObjectFactory.getGroup(result.getString(6)),
							result.getInt(7), result.getDate(8));
					return game;
				} catch (UserNotExistingException e) {
					e.printStackTrace();
					logger.error(e);
					logger.error("Failed to read game from DB...");
				}
			}
			ObjectFactory.ds.getConnection().close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
